A database is an organized collection of data. Databases commonly organize the data into spaces, tables, indexes, columns, and rows. For example, a column (also referred to as a field) may represent an individual datum, such as a first name or a last name. Columns having some relation are stored together in a table. For example, a first name, last name, birthdate, and address field may be stored together in a table (often referred to as a table space) that describes a person. The actual instances of data are stored in a row. In the example above, “Judy” “Smith” “Oct. 2, 1975” may be a row in the described table. An index stores a subset of the columns in a table and is used to more quickly access a row of the table.
In some instances, database table spaces may be divided into partitions, with each partition capable of being processed independently by database utilities. Such partitioning is often used to improve table availability, performance, and maintenance. In a partitioned table space, each partition may be assigned a high key value, also known as a limit key, and the database system may assign rows to a partition using these limit key values. For example, in a database with four partitions, the first partition may store rows with a last name between “Aanuk” and “Graham,” the second partition may store rows with a last name between “Graham” and “Lewis,” the third partition may store rows with a last name between “Lewis” and “Smith,” and the last partition may store the remaining names. In such an example, the first partition may have a limit key of “Graham”, the second partition may have a limit key of “Lewis,” the third partition may have a limit key of “Smith” and the remaining partition may have a limit key of “Zwillig,” “ZZZ,” or high values (in some database environments, the limit key of the final partition may be ignored).
The value of the limit key for each partition may be changed by a database administrator for various reasons, such as to re-balance the partitions. In systems that alter the limit key of each partition using a single command, this process is fairly straightforward. But in systems that require an alter command for each partition, determining the order of the alter commands for the various partitions can be complex. For example in a DB2 database system, if a database administrator attempts to issue an alter command for a partition that changes the limit key to a value higher than the next partition's current limit key, the command will fail with a sequencing error. For example, consider the limit key changes represented in the following table, with column A representing the current limit key value for each partition and column B representing the new (or desired) limit key value:
PartitionCurrent LimitNew LimitNumberKeyKey120010023001753500550460060057001200610001500
Using a first-to-last strategy for executing the alter commands the first partition can be immediately changed to 100 because the second partition's existing key is 300 (the new value of 100<300). The second partition can be changed from 300 to 175 because partition 3 currently has a value of 550 (175<550). The fourth partition is not being changed. However, the fifth partition's key of 700 cannot be altered to 1200 because the current key of partition 6 is 1000 (1200>1000); thus, the ALTER command for partition 5 would fail with an out-of-sequence condition. For this to be a valid sequence, partition 6 must first be altered to 1500. Then partition 5 can be changed to 1200.
Using a last-to-first strategy, partition 6 can be modified from 1000 to 1500 since it is greater than its current value and it is the last partition for the object. Partition 5 can be altered from 700 to 1200 since partition 6 has already been modified from 1000 to 1500 (1200<1500). No change was requested for partition 4. Partition 3 can now be changed from 500 to 550. However, partition 2 cannot be changed from 300 to 175 because the first partition's current value of 200 would cause an out-of-sequence condition. For these changes to be implemented, partition 1 first needs to be altered to 100 before the second partition can be changed to 175. Thus, in the example above, neither straightforward sequence solution (first-to-last or last-to-first) results in successfully updating the limit key values.
As the number of partitions increases, the task of determining the correct alter sequence for the partitions becomes increasingly difficult. The task may be further complicated by limit keys that include a combination of columns. For example, the limit keys for the partitions of a table may include three columns, a character column in descending order, a data column in ascending order, and an integer column in ascending order, making comparison of limit key values increasingly difficult. Furthermore, limit key changes are often performed at night or on weekends to minimize the impact of database down time on users. Thus, a failed attempt to update the limit keys may not be discovered right away. Additionally, a failed update attempt may incur complex and time-consuming restart processing that elongates the outage window for the affected database partitions.
To avoid the time and monetary costs associated with failed attempts at changing limit keys, a need exists for a system and method for determining whether the proposed new limit keys can result in a successful series of alter statements and, if so, determine the successful sequence order and automatically generate commands to execute the limit key changes.